2013-04-23-5768.200-Spec-BI Inventory Extraction Logic #SDMiscellaneousReports #ABAPEXTRACT

SPECIFICATIONS

-5768.200 BI Inventory Extract Logic

Purpose

---

Technical Documentation BI Inventory Extraction Function Module Logic

Admin Info

---

Purpose	Technical Documentation BI Inventory Extraction Function Module Logic
Requested By	Ashwin Kucheriya
Spec Created By	Venkat Kondapally
Spec Created Date	04/23/2013
Spec QA by	Venkat Kondapally
Objects	FUnction Modules:ZBI_GENERIC_INVEXT, ZBI_INVEXT_BI_FM2 ZBI_INVEXT_BI_FM
Document Status	Completed

Solution Summary

---

[Discuss this section with Requester and get approval prior to beginning work]

The function module "ZBI_GENERIC_INVEXT" is used to extract Inventory data from ECC. This Function Module is run on a daily basis.
The Function module calls two internal Function Modules,

1) ZBI_INVEXT_BI_FM2
2) ZBI_INVEXT_BI_FM

Solution Details

---

[Provide complete technical details for configuration or programming here]

Technical documentation :

ZBI_GENERIC_INVEXT: Generic Extract for inventory for BI

Input parameters:

• I_REQUNR Request number for the data transfer
• I_DSOURCE Info Source
• I_MAXSIZE Maximum number of table entries in extraction API interfaces
• I_INITFLAG Flag: Call up of the extraction APIs for initialization
• I_READ_ONLY
• I_REMOTE_CALL Default value (SBIWA_C_FLAG_OFF)

TABLES Parameters

• I_T_SELECT (Input)
• I_T_FIELDS (Input)
• E_T_DATA BI Inventory Extract for BI Generic Data source (Structure) (Output)

Logic 1: Inventory Data:
If ZCHK = 'X'. (Selection Criteria)

• Get Inventory data using Function module ' ZBI_INVEXT_BI_FM2 '.

If ZCHK is blank or not equal to ‘X’.

• Get Inventory data using Function module ' ZBI_INVEXT_BI_FM '.

NOTE: Logic for above two documents are explained in a separate cover

Logic 2: UPC Numbers
For all records from inventory data (logic 1),

• Get UPC Number , Grid Value & Material from MEAN table ( International Article Numbers (EANs) for Material ) based on Material Number (Logic 1)

Logic 3: Season and Collection
For all records from inventory data (logic 1),

• Get Material Number, Season, Grid value & Collection from J_3AMSEA table ( AFS Material master seasons ) based on Material Number (Logic 1 )

Logic 4: Standard Price
For all records from inventory data (logic 1),

• Get Material Number, Valuation Area ,Valuation Type & Standard Price from (MBEW) table (Material Valuation ) based on Material Number & Plant from (logic 1)

Logic 5: Material Groups
For all records from inventory data (logic 1),

• Get Material Number, Material group 1, Material group 2,Material group 3 & Exclude from ROC from MVKE table ( Sales Data for Material ) based on Material Number From (Logic 1).

Logic 6: Condition Records
For all records from inventory data (logic 1),

• Get all Fields from (/AFS/DPRG_KOND) table (Condition Records Season Determination).

Logic 7: Plant
For all records from inventory data (logic 1),

• Get Plant Sales organization for inter company billing from T001W table (Plants/Branches).

Output

1. Blocked Quantit: Logic 1 – From FM ZBI_INVEXT_BI_FM2
2. Quantity on Hand: Logic 1 – From FM ZBI_INVEXT_BI_FM2
3. RECEIPTS1 (Current Period): Logic 1 – From FM ZBI_INVEXT_BI_FM2
4. RECEIPTS2 (Second Period): Logic 1 – From FM ZBI_INVEXT_BI_FM2
5. RECEIPTS3 (Third Period): Logic 1 – From FM ZBI_INVEXT_BI_FM2
6. INVEN_VALUE: Logic 1 – From FM ZBI_INVEXT_BI_FM2
7. PRICE: Logic 1 – From FM ZBI_INVEXT_BI_FM2
8. QTY_HAND_PREV: Logic 1 – From FM ZBI_INVEXT_BI_FM2
9. INVEN_VALUE_PREV: Logic 1 – From FM ZBI_INVEXT_BI_FM2
10. AVG_STOCK: Logic 1 – From FM ZBI_INVEXT_BI_FM2
11. MATNR (Material): Logic 1 – From FM ZBI_INVEXT_BI_FM2
12. PLANT: Logic 1 – From FM ZBI_INVEXT_BI_FM2
13. ZCOLOR (Color): Logic 1 – From FM ZBI_INVEXT_BI_FM2
14. MEINS (Base UOM): Logic 1 – From FM ZBI_INVEXT_BI_FM2
15. MRP_GRP: Logic 1 – From FM ZBI_INVEXT_BI_FM2
16. WAERS (Currency Key): Logic 1 – From FM ZBI_INVEXT_BI_FM2
17. BUKRS (Company Code): Logic 1 – From FM ZBI_INVEXT_BI_FM2
18. LAST_CON_DATE: Logic 1 – From FM ZBI_INVEXT_BI_FM2
19. LAST_REC_DATE: Logic 1 – From FM ZBI_INVEXT_BI_FM2
20. UPC Logic 2 MEAN-EAN11
21. SEASON: Logic 3 J_3AMSEA-J_3ASEAN
22. COLLECTIONS: Logic 3 J_3AMSEA-/AFS/COLLECTION
23. FIRSTPLSH: Logic 7 T001W-J_3ADLDV
24. LASTPLSH: Logic 7 T001W-J_3ADLDB
25. ZMATGRP1: Logic 5 MVKE-MVGVR1
26. ZMATGRP2: Logic 5 MVKE-MVGVR2
27. ZMATGRP3: Logic 5 MVKE-MVGVR3
28. ROC_FLAG: Logic 5 MVKE-PRAT2
29. ZSTDCOST: Logic 4 MBEW-STPRS
30. QTY_HAND:Logic 1 – From FM ZBI_INVEXT_BI_FM
31. QTY_ALLOC:Logic 1 – From FM ZBI_INVEXT_BI_FM
32. QTY_BLOCKED: Logic 1 – From FM ZBI_INVEXT_BI_FM
33. QTY_ORD: Logic 1 – From FM ZBI_INVEXT_BI_FM
34. QTY_PO_ORD: Logic 1 – From FM ZBI_INVEXT_BI_FM
35. QTY_AVAIL: Logic 1 From FM ZBI_INVEXT_BI_FM

---

Technical documentation :

ZBI_INVEXT_BI_FM2: Process Monthly Inventory Extract

Input Parameter:

• ZCHK

TABLES Parameters:

• S_MATNR (Input)
• S_WERKS (Input)
• T_INVEN_01 BI Inventory Extract for BI Generic Data source (Structure) (Output)

The FM is selected when the input field (WA_ZCHK) is set to X.

LOGIC A: Active Finished Goods Materials

• Get Plant(MARC-WERKS), Material Number(MARA_MATNR), Material Description(MAKT-MAKTX), Unit Of Measures (MARA-MEINS), Deletion Indicator (MARA-LVORM), Material Type(MARA-MTART), Deletion Indicator (MARC-LVORM) , Master Grid Number (MARA-j_3apgnr ), Color (MARA-J_3ACOL), MRP Group(MARC-DISGR) from MARC table (Plant Data for Material) , MARA table and MAKT table based on Plant(selection criteria) and Material Number (Selection Criteria)

Note: All three tables are joined to fetch data

• Data from above logic is filtered based on Material Type. Only materials with Material Type = ‘FERT’are considered
• Inactive materials are filtered out (Materials with Deletion Indicator are removed.

LOGIC B: Company Code

• Get Valuation Area( Plant : T001K-BWKEY), Company Code (T001-BUKRS) and Currency Key (T001-WEARS) based on Valuation Area = Plant

Logic C: Batch Stock and Sales Order Stock

• Get Material, Werks, Valuated Unrestricted-Use Stock (CLABS) and Blocked Stock (CSPEM) from MCHB Table (Batch Stock) based on Material and Plant from logic 1.
  o Both Valuated Unrestricted-Use Stock and Blocked Stock are summarized at Material and Plant Level
• Get Material, Plant, Valuated Unrestricted-Use Stock (KALAB) from MSKA Table ( SALES ORDER Stock) based on Material and Plant from logic 1

LOGIC D: Order Data

• Current and Next two fiscal periods are determined using some standard function modules.
• Get Order Number, Order Item Number, Material No, Delivery Completed Indicator, Scheduled finish Date and Plant from AFPO Table (Order item) based on Material (Selection Criteria), Plant and Delivery Completion Indicator = Blank.
• Filter the data which doesn’t fall in the three fiscal periods calculated above.
• Data is divided into three internal tables IT_AFPO1, IT_AFPO2 and IT_AFPO3 based on the three Fiscal Periods

LOGIC E:
Prod. Order QTY

• Get Material Number, Plant, Stock type, Stock/Batch Number, Item Number, Schedule line Number, Net SKU Quantity (J_3ABSSI-J_3AERFMG) , AFS Delivered Net Quantity (J_3ABSSI-J_3AGLMG), AFS Delivery Completed Indicator(J_3ABSSI-J_3AELIKZ), Deletion indicator(J_3ABSSI-LOEKZ) and AFS Indicator technical completed(J_3ABSSI-J_3ATCKZ) from J_3ABSSI (AFS STOCK (Sizes)) table based on Stock Type ( J_3ABSSI = ‘F’, Prod Order) and Order No ( logic 1 , Current period IT_AFPO1-Aufnr)

o Filter Data where delivery is completed
o Filter Data where deletion indicator is set
o Filter Data where AFS Indicator technical completed is set
o Summarize Net SKU Quantity and AFS Delivered Quantity at Material Plant level

PO/STO QTY

• Get Material, Plant, and Stock type, Stock Number (Batch Number or Procurement Proposal)Item Number, Schedule line, Net SKU Quantity,AFS Delivering Plant,Net Quantity,Schedule line date, Deletion indicator & AFS Indicator technical completed from J_3ABSSI (AFS STOCK (Sizes)) table based on Material & Plant and Stock Type = ‘B’ (Purchase Order ).

o Summarize Net SKU Quantity and AFS Delivered Quantity at Material Plant level

Note: Repeat Logic e for other two fiscal periods


Logic F: Date of Last Consumption and Date of Last Recipient

• Get Plant, Material, Date: last (i.e. most recent) consumption & Date: Last Receipt from S032 table (Statistics: Current Stock and Grouping Terms) based on Material and Plant (Logic 1).

Logic G: Inventory Value

• Get Material Number, Valuation Area, Valuation Type, Total Valuated Stock, Value of Total Valuated Stock, Price control indicator, Moving Average Price/Periodic Unit Price & Standard price From MBEW table (Material Valuation) based on Material and Plant (Logic 1).

Logic H: Get Inventory Value for previous period

• Calculate Last Period = Current Period - 13
• Calculate Previous based on Last Period

For eg, If current period is 06/2013, then the last period will be 05/2012

• Get Material Number, Valuation Area, Fiscal Year of Current Period, Current period (posting period),Standard Price(STPRS) Value of Total Valuated Stock & Total Valuated Stock from MBEH table (Material Valuation: History) Based on Material, Plant and Year ( Greater than Equal to Previous Year).

• Get Material Number, Plant, Fiscal Year of Current Period, Current period (posting period), Valuated Unrestricted-Use Stock (MSKAH-KALAB) from MSKAH table (Sales Order Stock: History) Based on Material, Plant and Year (Previous Year).

• QTY_HAND_PREV: Total Valuated Stock (MBEWH) + Unrestricted Stock ( MSKAH ) - only data Of Previous Period
• AVG_STOCK = ( Total Valuated Stock (MBEWH) + Unrestricted Stock ( MSKAH ) ) /13 - for all 13 months from Previous period through last period

Output:
1. Blocked Quantity: MCHB-CSPEM (Logic C)
2. Quantity on Hand: Total Valuated Stock from (MBEW-LBKUM, Logic G) - Blocked quantity (MCHB-CSPEM (Logic C) + Unrestricted Stock (MSKA)(Logic C) .
3. RECEIPTS1 (Current Period): Net SKU Quantity (J_3ABSSI-J_3AERFMG, Logic E) - AFS Delivered Quantity (J_3ABSSI-J_3AGLMG, Logic E) + Net Quantity (J_3ABSSI-MENGE, Logic E )
4. RECEIPTS2 (Second Period): Net SKU Quantity (J_3ABSSI-J_3AERFMG, Logic E ) - AFS Delivered Quantity (J_3ABSSI-J_3AGLMG, Logic E) + Net Quantity (J_3ABSSI-MENGE, Logic E )
5. RECEIPTS3 (Third Period): Net SKU Quantity (J_3ABSSI-J_3AERFMG, Logic E) - AFS Delivered Quantity (J_3ABSSI-J_3AGLMG, Logic E) + Net Quantity (J_3ABSSI-MENGE, Logic E)
6. INVEN_VALUE: Value of Total Valuated Stock (MBEW-SALK3 Logic G) + Unrestricted Stock ( MSKA-KALAB Logic c ) * Standard Price ( MBEW-STPRS logic G)
7. PRICE: Standard price (MBEW-STPRS Logic G )
8. QTY_HAND_PREV: Logic H
9. INVEN_VALUE_PREV: Value of Total Valuated Stock (MBEWH-SALK3 Logic H) + Unrestricted Stock ( MSKAH-KALAB logic H) * Standard price (MBEWH-STPRS Logic H)
10. AVG_STOCK: Logic H
11. MATNR (Material): MARA-MATNR (Logic A)
12. PLANT: MARC-WERKS (Logic A)
13. ZCOLOR (Color): MARA-J_3ACOL (Logic A)
14. MEINS (Base UOM): MARA-MEINS(Logic A)
15. MRP_GRP: MARC-DISGR (Logic A)
16. WAERS (Currency Key): T001-WAERS (Logic B)
17. BUKRS (Company Code): T001-BUKRS(Logic B)
18. LAST_CON_DATE: S032-LETZTVER (Logic F)
19. LAST_REC_DATE: S032-LETZTVER (Logic F)

---

Technical documentation : ZBI_INVEXT_BI_FM

ZBI_INVEXT_BI_FM: Process Monthly Inventory Extract

TABLES

• S_MATNR (Input)

• S_WERKS (Input)

• T_INVEN_01 BI Inventory Extract for BI Generic Data source (Structure) (Output)

The FM is selected when the input field (ZCHK) is blank.

LOGIC I: Active Finished Goods Materials

• Get Plant(MARC-WERKS), Material Number(MARA_MATNR), Material Description(MAKT-MAKTX), Unit Of Measures (MARA-MEINS), Deletion Indicator (MARA-LVORM), Deletion Indicator (MARC-LVORM) , Master Grid Number (MARA- J_3APGNR), Color (MARA-J_3ACOL), MRP Group(MARC-DISGR) from MARC table (Plant Data for Material) , MARA table and MAKT table based on Plant(selection criteria) , Material Number (Selection Criteria) and Grid Number ( Should not be Blank).

Note: All three tables are joined to fetch data

• Get Material Grid Number (J_3APGEN- J_3APGNR) and Grid Value (J_3APGEN-J_3AKORDX ) from AFS Product grid entries Table (J_3APGEN) table based on Material Grid Number ( J_3APGEN- J_3APGNR) ( above logic ).

• Inactive materials are filtered out (Materials with Deletion Indicator are removed.

Raw Materials

• Get Plant(MARC-WERKS), Material Number(MARA_MATNR), Material Description(MAKT-MAKTX), Unit Of Measures (MARA-MEINS), Deletion Indicator (MARA-LVORM), Deletion Indicator (MARC-LVORM) , Master Grid Number (MARA- J_3APGNR), Color (MARA-J_3ACOL), MRP Group(MARC-DISGR) from MARC table (Plant Data for Material) , MARA table and MAKT table based on Plant(selection criteria) , Material Number (Selection Criteria) and Grid Number ( Should be Blank ).

Note: All three tables are joined to fetch data

• Inactive materials are filtered out (Materials with Deletion Indicator are removed.

Logic II: Batch Stock Finished Goods

• Get Material, Werks, Grid Value, Valuated Unrestricted-Use Stock (CLABS) and Blocked Stock (CSPEM) from MCHB Table (Batch Stock) based on Material and Plant from logic 1.

• Both Valuated Unrestricted-Use Stock and Blocked Stock are summarized at Material , Plant and Grid Level

Raw materials Stock from Storage Location

• Get Material, Werks, Valuated Unrestricted-Use Stock (CLABS) and Blocked Stock (CSPEM) from MARD Table (Storage Location Data for Material) based on Material and Plant from logic 1.

  • Both Valuated Unrestricted-Use Stock and Blocked Stock are summarized at Material , Plant Level

LOGIC III: Allocation Quantity to Orders

• Get Plant, Material, Grid Value, Quantity from J_3ABDBS Table (AFS Requirement - Stock assignment) based on Pant (Logic 1), Material (Logic 1), Customer (should be blank ) and MRP Status ( J_3ABDBS - J_3ASTAT = ‘D’ or ‘F’ or ‘R’ ).

• Summarize Quantity at Material, Plant and Grid level

LOGIC IV: Open Order QTY

• Get Plant (J_3ABDSI~WERKS), Material (J_3ABDSI~MATNR),Grid Value (J_3ABDSI~J_3ASIZE), Quantity (J_3ABDSI~MENGE ), AFS Fixed reserved quantity (J_3ABDSI~J_3ARESM ) from J_3ABDSI Table (AFS ORDER (Sizes) ) which is joined with Sales Header Data ( VBAK ) using Order Number, Material (Logic 1) and Plant (Logic 1).

• Summarize Quantity and AFS Delivered Quantity at Material , Plant and Grid level

LOGIC V: PO/STO QTY

• Get Material, Plant, Grid Value and Quantity from J_3ABSSI (AFS STOCK (Sizes)) table based on Material & Plant and Stock Type = ‘B’ (Purchase Order) or ‘F’.

• Summarize Quantity at Material, Plant and Grid level

Output:

1. MATNR (Material): MARA-MATNR (Logic I)

2. PLANT: MARC-WERKS (Logic I)

3. MEINS (Base UOM): MARA-MEINS(Logic I)

4. ZCOLOR (Color): MARA-J_3ACOL (Logic I)

5. J_3ASIZE(Size, Grid Value) MARA- J_3APGEN-J_3AKORDX(Logic I)

6. QTY_HAND: MCHB-CLAB or MARD-LABST (Raw Material) (logic II)

7. QTY_ALLOC: J_3ABDBS-MENGE ( Logic III)

8. QTY_BLOCKED: MCHB-CSPEM or MARD-SPEME (Raw material) (logic II)

9. QTY_ORD: J_3ABDSI-Menge (logic IV)

10. QTY_PO_ORD: J_3ABSSI-MENGE (Logic V)

11. QTY_AVAIL: (QTY_HAND - QTY_BLOCKED ) + QTY_ORD + QTY_ALLOC

12. MRP_GRP(MRP Group): MARC-DISGR(Logic I)